SQL parser
Otázka od: Lebeda David
30. 9. 2002 13:26
Ahoj,
hledam SQL parser, ktery mi z SQL dotazu (s joiny) dokaze sestavit seznam
dvojic
tabulka-jmeno. Jinou funkcnost nepotrebuji.
Napr.
select a.id, a.nazev, b.cena, c.* from tabulka1 a
left join tabulka2 b on bla bla
left join tabulka3 c on bla bla
Vysledek:
tabulka1 id
tabulka1 nazev
tabulka2 cena
tabulka3 id
tabulka3 nazev
tabulka3 kategorie
tabulka3 atd.
Vim, ze k rozlozeni hvezdicky na seznam sloupcu je treba spoluprace SQL
serveru,
to je OK. Vite nekdo o necem, co by aspon castecne resilo popsany problem? Ja
jsem sice neco stahl z internetu, ale na druhou stranu bych nerad stravil
nekolik dni
hledanim a zkousenim vsech nalezenych produktu.
D5E, FB1.0, IBX 4.52
Diky
David Lebeda
Odpovedá: Ondrej Kelle
30. 9. 2002 15:11
> hledam SQL parser, ktery mi z SQL dotazu (s joiny) dokaze
> sestavit seznam dvojic
> tabulka-jmeno. Jinou funkcnost nepotrebuji.
>
> Napr.
>
> select a.id, a.nazev, b.cena, c.* from tabulka1 a
> left join tabulka2 b on bla bla
> left join tabulka3 c on bla bla
>
>
> Vysledek:
>
> tabulka1 id
> tabulka1 nazev
> tabulka2 cena
> tabulka3 id
> tabulka3 nazev
> tabulka3 kategorie
> tabulka3 atd.
>
> Vim, ze k rozlozeni hvezdicky na seznam sloupcu je treba
> spoluprace SQL serveru,
> to je OK. Vite nekdo o necem, co by aspon castecne resilo
> popsany problem? Ja
> jsem sice neco stahl z internetu, ale na druhou stranu bych
> nerad stravil nekolik dni
> hledanim a zkousenim vsech nalezenych produktu.
>
> D5E, FB1.0, IBX 4.52
Ahoj, to, co Ti teraz poslem, nie je presne to, co si ziadal, ale predsa...
Kedze pises, ze pristup na server je OK, napadlo ma, ze mozes nechat server,
nech to rozparsuje pocas Prepare. isc_dsql_prepare totiz ponuka moznost
zistit info o stlpcoch, akurat TIBSQL tuto moznost nevyuziva a nepublikuje
navonok.
Ako si pisal, * nie je mozne riesit bez pristupu na server. Nasledovny kod
sa teda moze hodit (ak Ti nevadi, ze pristupuje na server vzdy - aj v
pripade, ked SQL neobsahuje znak '*'):
implementation
uses
IB, IBHeader, IBIntf, IBExternals, IBSQL, IBXConst;
// modified version of TIBSQL.PreprocessSQL (no need to generate param
names, just return preprocessed SQL)
// - needed because both PreprocessSQL and FProcessedSQL are private
function PreprocessSQL(const SQL: string): string;
var
cCurChar, cNextChar, cQuoteChar: Char;
i, iLenSQL, iSQLPos: Integer;
iCurState, iCurParamState: Integer;
const
DefaultState = 0;
CommentState = 1;
QuoteState = 2;
ParamState = 3;
ParamDefaultState = 0;
ParamQuoteState = 1;
procedure AddToProcessedSQL(cChar: Char);
begin
Result[iSQLPos] := cChar;
Inc(iSQLPos);
end;
begin
Result := '';
{ Do some initializations of variables }
cQuoteChar := '''';
iLenSQL := Length(SQL);
SetString(Result, nil, iLenSQL + 1);
i := 1;
iSQLPos := 1;
iCurState := DefaultState;
iCurParamState := ParamDefaultState;
{ Now, traverse through the SQL string, character by character,
picking out the parameters and formatting correctly for InterBase }
while (i <= iLenSQL) do begin
{ Get the current token and a look-ahead }
cCurChar := SQL[i];
if i = iLenSQL then
cNextChar := #0
else
cNextChar := SQL[i + 1];
{ Now act based on the current state }
case iCurState of
DefaultState: begin
case cCurChar of
'''', '"': begin
cQuoteChar := cCurChar;
iCurState := QuoteState;
end;
'?', ':': begin
iCurState := ParamState;
AddToProcessedSQL('?');
end;
'/': if (cNextChar = '*') then begin
AddToProcessedSQL(cCurChar);
Inc(i);
iCurState := CommentState;
end;
end;
end;
CommentState: begin
if (cNextChar = #0) then
IBError(ibxeSQLParseError, [SEOFInComment])
else if (cCurChar = '*') then begin
if (cNextChar = '/') then
iCurState := DefaultState;
end;
end;
QuoteState: begin
if cNextChar = #0 then
IBError(ibxeSQLParseError, [SEOFInString])
else if (cCurChar = cQuoteChar) then begin
if (cNextChar = cQuoteChar) then begin
AddToProcessedSQL(cCurChar);
Inc(i);
end else
iCurState := DefaultState;
end;
end;
ParamState:
begin
{ collect the name of the parameter }
if iCurParamState = ParamDefaultState then
begin
if cCurChar = '"' then
iCurParamState := ParamQuoteState
else if not (cCurChar in ['A'..'Z', 'a'..'z', '0'..'9', '_', '$'])
then
IBError(ibxeSQLParseError, [SParamNameExpected]);
end
else begin
{ determine if Quoted parameter name is finished }
if cCurChar = '"' then
begin
Inc(i);
iCurParamState := ParamDefaultState;
iCurState := DefaultState;
end;
end;
{ determine if the unquoted parameter name is finished }
if (iCurParamState <> ParamQuoteState) and
(iCurState <> DefaultState) then
begin
if not (cNextChar in ['A'..'Z', 'a'..'z',
'0'..'9', '_', '$']) then begin
Inc(i);
iCurState := DefaultState;
end;
end;
end;
end;
if iCurState <> ParamState then
AddToProcessedSQL(SQL[i]);
Inc(i);
end;
AddToProcessedSQL(#0);
end;
procedure GetQualifiedFieldNames(IBSQL: TIBSQL; FieldNames: TStrings);
overload;
var
PreprocessedSQL: string;
Buf: PXSQLDA;
BufSize, ItemCount: Integer;
I: Integer;
begin
if not Assigned(IBSQL) or not Assigned(IBSQL.Database) then
Exit;
PreprocessedSQL := PreprocessSQL(IBSQL.SQL.Text);
IBSQL.Prepare;
// find out column count
ItemCount := 0;
BufSize := XSQLDA_LENGTH(ItemCount);
Buf := AllocMem(BufSize);
try
Buf^.version := SQLDA_VERSION1;
Buf^.sqldabc := BufSize;
IBSQL.Call(isc_dsql_prepare(StatusVector, IBSQL.TRHandle, @IBSQL.Handle,
0, PChar(PreprocessedSQL),
IBSQL.Database.SQLDialect, Buf), True);
ItemCount := Buf^.sqld;
finally
FreeMem(Buf);
end;
// allocate enough memory and try again
if ItemCount > 0 then
begin
BufSize := XSQLDA_LENGTH(BufSize);
Buf := AllocMem(BufSize);
try
Buf^.version := SQLDA_VERSION1;
Buf^.sqldabc := BufSize;
Buf^.sqln := ItemCount;
IBSQL.Call(isc_dsql_prepare(StatusVector, IBSQL.TRHandle,
@IBSQL.Handle, 0, PChar(PreprocessedSQL),
IBSQL.Database.SQLDialect, Buf), True);
FieldNames.Clear;
for I := 0 to Buf^.sqld - 1 do
with Buf^.sqlvar[I] do
FieldNames.Add(Format('%s.%s', [relname, sqlname]));
finally
FreeMem(Buf);
end;
end;
end;
type
THackIBCustomDataSet = class(TIBCustomDataSet);
procedure GetQualifiedFieldNames(IBDataSet: TIBCustomDataSet; FieldNames:
TStrings); overload;
begin
GetQualifiedFieldNames(THackIBCustomDataSet(IBDataSet).QSelect,
FieldNames);
end;
procedure TForm1.Button1Click(Sender: TObject);
begin
IBDatabase1.Open;
IBTransaction1.Active := True;
GetQualifiedFieldNames(IBQuery1, Memo1.Lines);
end;
HTH
TOndrej
Odpovedá: Ondrej Kelle
30. 9. 2002 17:24
V procedure GetQualifiedFieldNames som mal preklep, tu je oprava:
// allocate enough memory and try again
if ItemCount > 0 then
begin
// blbost: BufSize := XSQLDA_LENGTH(BufSize);
BufSize := XSQLDA_LENGTH(ItemCount);
Buf := AllocMem(BufSize);
try
...
Sorry za tu chybu,
TOndrej
Odpovedá: Jan Tungli
4. 10. 2002 9:49
Pozri SQL Parse na staranke www.Tsoft.szm.sk ale je to na BDE. Koz mozes
rozsirit a ziskat co chces.
|| -----Original Message-----
|| From: Lebeda David [mailto:david.lebeda@comarr.cz]
|| Sent: Monday, September 30, 2002 12:51 PM
|| To: delphi-l@clexpert.cz
|| Subject: SQL parser
||
||
|| Ahoj,
||
|| hledam SQL parser, ktery mi z SQL dotazu (s joiny)
|| dokaze sestavit seznam dvojic
|| tabulka-jmeno. Jinou funkcnost nepotrebuji.
||
|| Napr.
||
|| select a.id, a.nazev, b.cena, c.* from tabulka1 a
|| left join tabulka2 b on bla bla
|| left join tabulka3 c on bla bla
||
||
|| Vysledek:
||
|| tabulka1 id
|| tabulka1 nazev
|| tabulka2 cena
|| tabulka3 id
|| tabulka3 nazev
|| tabulka3 kategorie
|| tabulka3 atd.
||
|| Vim, ze k rozlozeni hvezdicky na seznam sloupcu je treba
|| spoluprace SQL serveru,
|| to je OK. Vite nekdo o necem, co by aspon castecne
|| resilo popsany problem? Ja
|| jsem sice neco stahl z internetu, ale na druhou stranu
|| bych nerad stravil nekolik dni
|| hledanim a zkousenim vsech nalezenych produktu.
||
|| D5E, FB1.0, IBX 4.52
||
|| Diky
||
|| David Lebeda
||